# coding=utf-8
'''
Created on 2018年1月4日

@author: lihhz
'''
import configparser
import os,sys

import cx_Oracle
# Python 3.6.4rc1 (v3.4.4rc1:04f3f725896c, Dec  6 2015, 17:06:10) [MSC v.1600 64 bit (AMD64)] on win32
# cx_Oracle-6.1
from docx import Document

#获取文件的当前路径(绝对路径)
current_path = os.path.dirname(os.path.realpath(sys.argv[0]))
#获取conf.ini的路径(绝对路径)
config_path = os.path.join(current_path, 'conf.ini')

#读取配置文件
config = configparser.ConfigParser()
config.read(config_path)
host = config.get("global", "host")
port = config.get("global", "port")
sid = config.get("global", "sid")
user = config.get("global", "user")
passwd = config.get("global", "passwd")
print("数据库配置如下:")
print(host+","+port+","+sid)

dsn = cx_Oracle.makedsn(host, port, sid)
connection = cx_Oracle.connect(user, passwd, dsn)
cursor = cx_Oracle.Cursor(connection)  # 返回连接的游标对象
tableNameList = []
document = Document()

document.styles['Normal'].font.name = u'宋体'
##document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')

print("1.查看都有哪些表")
sql = "select * from user_tables t order by t.TABLE_NAME asc"
if True:
    cursor.execute(sql)
    for i in cursor:
        tableNameList.append(i[0])
print(tableNameList)
print("2.正在写word文档,请稍候...")
for tableName in tableNameList:
    sql1 = """select t.comments from user_tab_comments t where t.table_name='"""+tableName+"""'"""
    if True:
        cursor.execute(sql1)
        for row in cursor:
            tabComment = row[0]
    sql = """select t1.column_name,t1.comments,t1.DATA_TYPE,t1.NULLABLE,
                       case when t2.position is not null then 'T' else 'F' end pk
                  from (select DISTINCT ucc.table_name,ucc.column_name,ucc.comments,
                                        utc.DATA_TYPE || '(' || utc.DATA_LENGTH || ')' DATA_TYPE,
                                        decode(utc.NULLABLE, 'Y', 'T', 'N', 'F') NULLABLE,utc.COLUMN_ID
                          from user_col_comments ucc, User_Tab_Columns utc
                         where utc.COLUMN_NAME = ucc.column_name
                           and ucc.table_name = '"""+tableName+"""'
                           AND UTC.TABLE_NAME = '"""+tableName+"""') t1
                  left join (select * from user_cons_columns uccs where uccs.position is not null
                                AND UCCS.table_name = '"""+tableName+"""') t2
                    on t1.table_name = t2.table_name
                   and t1.column_name = t2.column_name
                 order by t1.COLUMN_ID

        """
    if True:
        cursor = cursor.execute(sql)
        heading = tableName
        if tabComment:
            heading = heading + "("+tabComment + ")"
        paragraph = document.add_heading(heading, 2)
        table = document.add_table(rows=1, cols=5, style='Table Grid')
        hdr_cells = table.rows[0].cells
        hdr_cells[0].text = '字段'
        hdr_cells[1].text = '中文名称'
        hdr_cells[2].text = '数据类型'
        hdr_cells[3].text = '非空'
        hdr_cells[4].text = '主键'
        for row in cursor:
            row_cells = table.add_row().cells
            index = 0
            for col in row:
                row_cells[index].text = str(col)
                index = index + 1
                if index == 5:
                    break
    #print(sql)
document.save(u'数据库设计文档.docx')
print("")
print("3.写word文档结束,退出")
